In SSIS Slowly Changing Dimension (or) SCD is categorized into 3 parts.
Select this type when Changed Values should overwrite With existing values
Select this type When Changes in particular Columns Values Are saved as new records and Previous values are Saved as expired records.
Select this type when the value in a column Should not change. Changes are treated as errors.
Open Business Intelligence Development Studio ---------->
Create a new package and rename it as an scd. Dtsx
In Control flow drag and drop Data Flow Task and rename it as DFTSCD ---------->
Open SQL Server Management Studio and run the following SQL Statements ---------->
To create temporary tables for source and destination ---------->
Table for Source Select _ into Human Resources. Employee Address from Human Resources. Employee ---------->
In Data flow drag and drop OLEDB source ---------->
Double click on OLEDB Source to configure it ---------->
Provide Connection Manager if exists ---------->
Select Human Resources. Employee Address from the drop-down list ---------->
Drop and Drop SCD and make a connection from OLEDB Source to SCD ---------->
Double click on SCD to configure it ---------->
In welcome to the slowly changing Dimension wizard, click next ---------->
Provide destination connection manager and select Human Resources. Employee destination and set,
Login ID – Business Key ---------->
Click Next ---------->
Birth Date - Fixed Attribute
Gender - Fixed Attribute
Contact ID - Fixed Attribute
Hire Date - Fixed Attribute
Marital Status - Historical Attribute
Sick Leave Hours - Historical Attribute
Title - change Attribute
Inclined to build a profession as MSBI Developer? Then here is the blog post on, explore MSBI Training
Click Next ---------->
Make sure that Fail the transformation, if changes are detected in the Fixed attribute checkbox is checked ---------->
Click Next ---------->
Use a single column to show Current and Expired records radio button ---------->
Column to indicate current Record Select Nation ID Number ---------->
Value When Current - Select Current Expiration value - Expired ---------->
Click Next ---------->
Click finish
Note:
For Cross – Testing SCD, Make some changes in the source table for a fixed
Attribute (update [Human Resource].[Employee] ) set Gender = ‘M’ Where employee ID = 1 ---------->
Execute package
It takes a fixed number of rows from the Source data set.
It splits the data set into 2 sets, Sample and UN sampled output.
Note:
The main usage of this transformation would be to select a fixed size subset Of data. This subset can be used to test the Packages with a limited data set.
For example:
If you are running a package against a multimillion rows, you could Just run the package with.
Open Business Intelligence Development Studio ---------->
Create a New package and rename it as Row Sampling. dtsx ---------->
In Control flow drag and drop data flow task ---------->
In Data Flow drag and drop OLEDB Source ---------->
Double click on OLEDB Source to configure it ---------->
Provide Connection Manager if exists ---------->
Select Human Resources. Employee Address from the drop-down list ---------->
Select Columns and click ok ---------->
Drag and Drop Row Sampling transformation and make a connection from ---------->
Source to Row Sampling. ---------->
Double click on Row sampling to configure it and set, ---------->
Click ok ---------->
Drag and Drop OLEDB destination and make a connection from Row Sampling to Destination ---------->
Select Sampling Selected output in Input-output Selection Editor ---------->
Click ok ---------->
Double click on OLEDB destination to configure it ---------->
Provide destination connection manager and click new to create destination table ---------->
And rename it as Row Sample ---------->
Click ok twice
Note:
If you want to test your package with the sample data set, use Random seed.
percentage Sampling Splits a data set by read-only directing Rows to one of two outputs. We need to set the percentage of rows splits in to the 2 outputs (Sampling and Un sampling)
Steps to Configure percentage Sampling
Open Business Intelligence Development Studio ---------->
Create a new package and rename it as Percentage Sampling .dtsx ---------->
In Data Flow drag and drop the OLEDB source and configure it. ---------->
Drag and Drop percentage Sampling and make a connection from Source to percentage Sampling ---------->
Double click on percentage sampling to the editor it and Specify, ---------->
Percentage of row - 10 ---------->
Click ok
Drag and Drop the OLEDB destination and Configure it.
Audit Transformation is used to display Audit information such as package Name, Execution Start time, Task Name, User Name, Machine Name, Task ID, etc.,
Copy column transformation is used to copy input columns.
Example: Gender - Copy of Gender
Character Map Transformation is used to apply string operations in the selected data set.
Create a New package ---------->
In Data Flow drag and drop OLEDB Source ---------->
Double click on OLEDB Source to configure it ---------->
Provide connection Manager if exits and Select Human Resources. Employee From the drop-down list ---------->
Drag and drop Audit transformation and make a connection from the source to audit. ---------->
Double click on audit and Select the following ---------->
Audit Type,
Package Name
Task Name
Machine Name
User Name
Click ok ---------->
Drag and Drop copy column and make a connection from Audit to copy Column ---------->
Select Gender from available input columns and click ok ---------->
Drag and drop Character Map transformation and make a column to Character Map ---------->
Double click on Character Map and check the copy of the Gender available input column and set, ---------->
Operation - Lower Case ---------->
Click ok
Drag and Drop OLEDB destination and make a connection from Character Map to destination ---------->
Double click on the destination and provide destination connection manager ---------->
Click new to create destination table and rename it as Audit data ---------->
Click ok twice
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
